from sqlalchemy import exc, or_, desc, text, func
from sqlalchemy.orm import Session

from model.TeachingModel import NoticeModel
from schema.QuerySchema import NoticeQuery, QueryBase
from schema.TeachingSchema import NoticeInfo
from util.EmailHelper import EmailHelper
from util.ErrorCode import ErrorCode
from util.LogHelper import Log


def send_notice(noticeInfo: NoticeInfo, db: Session):
    notice_members_name = create_notice_members_name(noticeInfo.notice_members, 'student', db)
    notice_copy_members_name = create_notice_members_name(noticeInfo.notice_copy_members, 'employee', db)
    noticeModel = NoticeModel(user_id=noticeInfo.user_id, notice_topic=noticeInfo.notice_topic,
                              notice_content=noticeInfo.notice_content, notice_members=noticeInfo.notice_members,
                              notice_members_name=notice_members_name,
                              notice_copy_members=noticeInfo.notice_copy_members,
                              notice_copy_members_name=notice_copy_members_name)
    try:
        if EmailHelper.sendEmail(noticeInfo.notice_members, noticeInfo.notice_copy_members, noticeInfo.notice_topic,
                                 noticeInfo.notice_content):
            noticeModel.status = 2
            db.add(noticeModel)
            db.commit()
            return {'code': ErrorCode.SUCCESS.value, 'message': 'success'}
        else:
            noticeModel.status = 3
            db.add(noticeModel)
            db.commit()
            return {'code': ErrorCode.SEND_EMAIL_ERROR.value, 'message': 'send email failed!'}
    except exc.SQLAlchemyError as e:
        db.rollback()
        Log.getlogger().error(f'add_notice failed:{noticeInfo} message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def mod_notice_status(notice_id: int, status: int, db: Session):
    noticeModel = db.query(NoticeModel).filter(NoticeModel.id == notice_id).one_or_none()
    if noticeModel is not None:
        noticeModel.status = status
        noticeModel.occurtime = func.now()
        try:
            db.commit()
            return {'code': ErrorCode.SUCCESS.value, 'message': 'success'}
        except exc.SQLAlchemyError as e:
            Log.getlogger().error(f'mod_notice_status failed! notice_id:{notice_id} message: {e}')
            return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}
    else:
        Log.getlogger().error(f'id:{notice_id} is not exists!')
        return {'code': ErrorCode.DATABASE_RECORD_NOT_FOUND.value, 'message': 'record not exists!'}


def query_notice_by_page(query_base: QueryBase, db: Session):
    try:
        total_count = db.query(NoticeModel).count()
        total_pages = (total_count + query_base.limit - 1) // query_base.limit

        result = db.query(NoticeModel).order_by(desc(NoticeModel.occurtime)).offset(
            (query_base.skip - 1) * query_base.limit).limit(query_base.limit).all()

        return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'total_count': total_count,
                'total_pages': total_pages, 'data': result}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(f'query_notice_by_page failed!  message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def query_notice_by_field(noticeQuery: NoticeQuery, db: Session):
    try:
        total_count = db.query(NoticeModel).filter(
            or_(NoticeModel.user_id == noticeQuery.user_id, noticeQuery.user_id == 0)).count()
        total_pages = (total_count + noticeQuery.query_base.limit - 1) // noticeQuery.query_base.limit

        result = db.query(NoticeModel).filter(
            or_(NoticeModel.user_id == noticeQuery.user_id, noticeQuery.user_id == 0)).offset(
            (noticeQuery.query_base.skip - 1) * noticeQuery.query_base.limit).limit(noticeQuery.query_base.limit).all()

        return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'total_count': total_count,
                'total_pages': total_pages, 'data': result}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(f'query_notice_by_field failed! user_id: {noticeQuery.user_id}  message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def query_notice_by_id(notice_id: int, db: Session):
    try:
        result = db.query(NoticeModel).filter(
            or_(NoticeModel.id == notice_id, notice_id == 0)).one_or_none()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'data': result}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(f'query_notice_by_field failed! id: {notice_id}  message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def create_notice_members_name(notice_members: str, table_name: str, db: Session):
    if notice_members is not None:
        if len(notice_members) != 0:
            email_list = notice_members.split(';')
            email_list = list(filter(None, email_list))
            email_list_with_quotes = [f"'{email}'" for email in email_list]
            emails = ','.join(email_list_with_quotes)
            query = text(
                "SELECT GROUP_CONCAT(name SEPARATOR ', ') AS names FROM " + table_name + " where email in (" + emails + ")")
            result = db.execute(query).fetchone()
            return result[0]

        else:
            return ''
    else:
        return ''
